/*
 * Event_DBImpl.java
 *
 * Created on January 4, 2007, 8:02 PM
 */

package com.asaga.tools;

import java.beans.*;
import java.io.Serializable;
import java.text.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.*;
import javax.mail.*;
import javax.faces.application.FacesMessage;
import javax.faces.component.UIComponent;
import javax.faces.component.UIInput;
import javax.faces.context.FacesContext;

 
/* 
 * Event Databse Implementation
 * @author mmixon
 */

/**
 * Event DB Implementation
 */
public class Event_DBImpl extends Object implements Event {
    private int id;
    private String title;
    private String startDate;
    private String endDate;
    private String startTime;
    private String endTime;
    private String description;
    private int classId;
    private int locationId;
    private int groupId;
    private int creatorId;
    private String warningMessage;
    private DBPool connectionPool;
    private Connection connection;
    private Location_DBImpl location;
    private double rating;
    
    /**
     * Event_DBImpl Constructor
     */
    public Event_DBImpl() {
        //constructor
        connectionPool = DBPool.getInstance();
        groupId = 0;
        creatorId = 0;
        locationId = 0;
        warningMessage = "";
        setLocation(new Location_DBImpl());
    }
    
    
    /**
     * Add Event Method
     * inserts the event to into the database
     */
    public void addEvent() {
        connection = connectionPool.getConnection();
        String insertEventSQL = "insert into event (title, startDate, endDate, " +
                "startTime, endTime, description) values (?,?,?,?,?,?)";
        String creatorSQL = "insert into person_has_event (person_personID, event_eventID, " +
                "dateCreated, creator, willAttend) values " +
                "(?,?, (SELECT CURRENT_TIMESTAMP()),?,?)";
        String locationSQL = "insert into event_has_location (event_eventID, location_locationID) values" +
                " (?,?)";
        String groupSQL = "insert into event_has_autismgroup (event_eventID, autismgroup_autismgroupID) values" +
                " (?,?)";
        String getGroupsSQL = "select autismgroup_groupID as id from person_has_autismgroup where person_personID = " + creatorId;
        try{
            //insert event
            PreparedStatement ps = connection.prepareStatement(insertEventSQL);
            ps.setString(1, SQLUtil.encode(getTitle()));
            ps.setDate(2, (java.sql.Date)convertDate(getStartDate()));
            ps.setDate(3, (java.sql.Date)convertDate(getEndDate()));
            ps.setTime(4, convertTime(getStartTime()));
            ps.setTime(5, convertTime(getEndTime()));
            ps.setString(6, SQLUtil.encode(getDescription()));
            ps.executeUpdate();
            
            //set new id
            setNewId();
            
            //insert person_has_event
            ps = connection.prepareStatement(creatorSQL);
            ps.setInt(1, creatorId);
            ps.setInt(2, id);
            ps.setInt(3, creatorId);
            ps.setInt(4, 1);
            ps.executeUpdate();
            
            //insert event_has_location
            ps = connection.prepareStatement(locationSQL);
            ps.setInt(1, id);
            ps.setInt(2, locationId);
            ps.executeUpdate();
            
            //insert event_has_group
            java.sql.Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(getGroupsSQL);
            while(rs.next()){
                String groupInsertSQL = "insert into event_has_autismgroup (event_eventID, autismgroup_groupID)" +
                        " values (?,?)";
                ps = connection.prepareStatement(groupInsertSQL);
                int groupid = rs.getInt("id");
                ps.setInt(1, id);
                ps.setInt(2, groupid);
                ps.executeUpdate();
            }

            warningMessage = "";
        } catch(WrongFormatException wfe){
            //wrong date format
            warningMessage = "wrong date format";
            System.out.println(warningMessage + wfe.getMessage());
        }
        
        catch(SQLException se){
            //
            warningMessage = "wrong sql format";
            System.out.println(warningMessage + se.getMessage());
        }
        
        connectionPool.freeConnection(connection);
    }//end addEvent
    
    /**
     * Remove Event Method
     * @param creator -- holds id of event reator
     * @param id -- the id of the event to be removed
     */
    public void removeEvent(int id, int creator){
        connection = connectionPool.getConnection();
        
        //find event in system
        String checkSQL = "select * from event e,  person_has_event pe where " +
                "e.eventID = pe.event_eventID AND " + 
                "e.eventID = " + id + " AND " + 
                "pe.event_eventID = " + id + " AND " + 
                "pe.person_personID = " + creator + " AND " + 
                "pe.creator = " + creator + " AND " +
                "e.startDate > (select current_date())";
        
        //Remove Event SQL
        String removeEventSQL = "DELETE from event where" +
                " eventID = " + id;
        String removeELSQL = "DELETE FROM event_has_location where " +
                "event_eventID = " + id;
        String removePESQL = "DELETE FROM person_has_event where" +
                " event_eventID =  " + id + " AND " +
                "person_personID = " + creator + " AND " +
                "creator = " + creator;
        
        //Get those who marked event on calendar
        //will notify by email
        String notifySQL = "SELECT fname, lname, email from person p, person_has_event pe WHERE" +
                " p.personID = pe.person_personID AND " +
                " pe.willAttend = 1 AND" +
                " pe.creator != p.personID";
        
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet check = stmt.executeQuery(checkSQL);
            if(check.next()){
                //populate event
                ResultSet notify = stmt.executeQuery(notifySQL);
                stmt.executeUpdate(removeEventSQL);
                stmt.executeUpdate(removeELSQL);
                stmt.executeUpdate(removePESQL);
                warningMessage = "event successfully removed.";
                
                //notify attendees
                /*while(notify.next()){
                    String toAddr = "mmixon@uga.edu"; //notify.getString("email");
                    String from = "mmixon@uga.edu";
                    String subject = "ATTENTION: " + title + " has been cancelled";
                    String messageText = "Unfortunately the event you were scheduled" +
                            " to attend has been canceled.  Please update your records. \n" +
                            " Best Regards, \n\n" +
                            " Educate America Support Team.";
                    MailUtil.sendMail(toAddr, from, subject, messageText);
                }*/
            }
            else{
                warningMessage = "No Event in System";
            }
        }
        catch(SQLException se){
            warningMessage = "SQL Error in removeEvent: " + se.getMessage();
            //insert error here
        }
        /*catch(MessagingException me){
            warningMessage = "Email Error:  "  + me.getMessage();
        }*/
        
    }
    
    /**
     * Search Event Method
     * @param searchString -- holds the string of criteria to find associated events
     */
    public ResultSet searchEvent(String searchTitle, String searchCity, String searchState) {

        String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and " +
                 "e.title like '%" + searchTitle + "%' and" +
                 " e.startDate > (select current_date()) ORDER BY state";

         String citySQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and " +
                 "l.city like '%" + searchCity + "%' and" +
                 " e.startDate > (select current_date()) ORDER BY state";

         String cityEvntSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and " +
                 "e.title like '%" + searchTitle + "%' and" +
                 " l.city like '%" + searchCity + "%' and" +
                 " e.startDate > (select current_date()) ORDER BY state";

         String stateSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and " +
                 "l.state like '%" + searchState + "%' and" +
                 " e.startDate > (select current_date()) ORDER BY state";

        String evntCityStSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and " +
                 "e.title like '%" + searchTitle + "%' and" +
                 " l.city like '%" + searchCity + "%' and" +
                 " l.state like '%" + searchState + "%' and" +
                 " e.startDate > (select current_date()) ORDER BY state";
        
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            if(searchTitle != null && searchCity == null && searchState == null)
                rs = stmt.executeQuery(eventSQL);
            else if(searchTitle != null && searchCity != null && searchState == null)
                rs = stmt.executeQuery(cityEvntSQL);
            else if(searchTitle == null && searchCity != null && searchState == null)
                rs = stmt.executeQuery(citySQL);
            else if(searchTitle == null && searchCity == null && searchState != null)
                rs = stmt.executeQuery(stateSQL);
            else
                rs = stmt.executeQuery(evntCityStSQL);
            connectionPool.freeConnection(connection);
        }
        catch(SQLException se){
            warningMessage="SQL Error: ";
        }
        return rs;
    }
    
    /**
     * Convert Time Method
     * @param time -- the time string to be converted to MySQL time
     * @throws com.asaga.tools.WrongFormatException -- exception thrown due to wrong format
     * @return -- returns the converted time
     */
    public Time convertTime(String time) throws WrongFormatException {
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("hh:mm:ss a");
        try {
            date = (java.util.Date)formatter.parse(time);
        } catch (ParseException e) {
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("HH:mm:ss");
        java.util.Date dDate = java.sql.Time.valueOf(xdf.format(date));
        return (java.sql.Time)dDate;
        
    }
    
    /**
     * Revert Time Method
     * @param time -- String in java.sql.Time format to be converted
     * @throws com.asaga.tools.WrongFormatException -- thrown if string is of the wrong format
     * @return -- returns correctly formatted time (hh:mm:ss a)
     */
    public String revertTime(java.sql.Time time) throws WrongFormatException{
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("HH:mm:ss");
        try{
            String oldTime = time.toString();
            date = (java.util.Date)formatter.parse(time.toString());
            //String newTime = date.toString();
            //System.out.println(newTime);
        }
        catch(ParseException e){
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("hh:mm:ss a");
        String newDate = xdf.format(date);
        //java.util.Date dDate = java.sql.Time.valueOf(xdf.format(date));
        return newDate;
    }
    
    /**
     * Convert Date Function
     * @param dateTime date String tobe converted
     * @throws com.asaga.tools.WrongFormatException Throws exception if the string is of the wrong format
     * @return returns the converted date
     */
    public Date convertDate(String dateTime) throws WrongFormatException {
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
        try {
            date = (java.util.Date)formatter.parse(dateTime);
        } catch (ParseException e) {
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dDate = java.sql.Date.valueOf(xdf.format(date));
        return dDate;
    }
    
    /**
     * Revert Date Method
     * @param dateTime -- the date of java.sql.Date format to be converted
     * @throws com.asaga.tools.WrongFormatException -- thrown is format of string is wrong
     * @return -- returns correctly formatted date
     */
    public String revertDate(java.sql.Date dateTime) throws WrongFormatException{
        Date date = new Date();
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        try{
            date = (java.util.Date)formatter.parse(dateTime.toString());
        }
        catch(ParseException e){
            setWarningMessage("Wrong format.  Can't convert.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("MM/dd/yyyy");
        return xdf.format(date);
    }
    
    /**
     * Convert Gregorian Date Function
     * @return returns the converted date
     * @param date -- the date string to be converted to mysql date
     * @throws com.asaga.tools.WrongFormatException Throws exception if the string is of the wrong format
     */
    public Date convertGregorianDate(Date date) throws WrongFormatException{
        Date newDate = new Date();
        DateFormat formatter = new SimpleDateFormat("EEE MMM dd hh:mm:ss z yyyy");
        try{
            newDate = (java.util.Date)formatter.parse(date.toString());
        } catch(ParseException e){
            setWarningMessage("Wrong format for date.");
            throw new WrongFormatException(getWarningMessage());
        }
        
        SimpleDateFormat xdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date dDate = java.sql.Date.valueOf(xdf.format(newDate));
        return dDate;
    }
    
    /**
     * Event List Size Function
     * @return -- the number of events in the database
     */
    public int eventListSize(){
        connection = connectionPool.getConnection();
        String sizeSQL = "select count(*) as numOfEvents from event e";
        int size = 0;
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery(sizeSQL);
            result.next();
            size = result.getInt("numOfEvents");
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            // insert exception
        }
        return size;
    }
    
    /**
     * Set Temp Event Function
     * @param iterator -- sets temporary event with id as iterator
     */
    public void setTempEvent(int iterator) {
    }
    
    /**
     * Number of Events method
     * @return -- the number of events
     */
    public int numberOfEvents() {
        return eventListSize();
    }
    
    /**
     * Focus On Event Method
     * @param id -- focuses on event of specified id
     */
    public void focusOnEvent(int id) {
    }
    
    /**
     * Get Id Method
     * @return -- the eventID returned
     */
    public int getId() {
        return id;
    }
    
    /**
     * Set New ID Method
     * method sets the new id of the event being inserted
     */
    public void setNewId(){
        String newIDSQL = "select MAX(eventID) as theid from event"; 
        connection = connectionPool.getConnection();
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet result = stmt.executeQuery(newIDSQL);
            result.next();
            id = result.getInt("theid");
        }
        catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage();
        }
        connectionPool.freeConnection(connection);
    }
    
    /**
     * Set Id Method
     * @param id -- sets eventID to id
     */
    public void setId(int id) {
        this.id = id;
    }
    
    /**
     * Get Title Method
     * @return -- returns the title
     */
    public String getTitle() {
        return title;
    }
    
    /**
     * Set Title Method
     * @param title -- sets title attribute to title
     */
    public void setTitle(String title) {
        this.title = title;
    }
    
    /**
     * Get Start Date Method
     * @return returns startDate attribute
     */
    public String getStartDate() {
        return startDate;
    }
    
    /**
     * Set Start Date Method
     * @param startDate sets this.startDate to startDate
     */
    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }
    
    /**
     * Get End Date Method
     * @return -- returns endDate
     */
    public String getEndDate() {
        return endDate;
    }
    
    /**
     * Set End Date Method
     * @param endDate --sets this.endDate to endDate
     */
    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }
    
    /**
     * Get Start Time Method
     * @return -- returns startTime attribute
     */
    public String getStartTime() {
        return startTime;
    }
    
    /**
     * Set Start Time Method
     * @param startTime sets this.startTime to startTime
     */
    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }
    
    /**
     * Get End Time Method
     * @return -- returns endTime attribute
     */
    public String getEndTime() {
        return endTime;
    }
    
    /**
     * Set End Time Method
     * @param endTime -- sets this.endTime to endTime
     */
    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }
    
    /**
     * Get Description Method
     * @return -- returns the description attribute.
     */
    public String getDescription() {
        return description;
    }
    
    /**
     * Set Description Method
     * @param description -- sets this.description to description
     */
    public void setDescription(String description) {
        this.description = description;
    }
    
    /**
     * Get Class ID Method
     * @return -- returns the classification id of the event
     */
    public int getClassId() {
        return classId;
    }
    
    /**
     * Set Class Method
     * 
     * sets the classification id of the event
     * @param classId -- the classId to be set
     */
    public void setClassId(int classId) {
        this.classId = classId;
    }
    
    /**
     * Get Location ID Method
     * @return returns the location id
     */
    public int getLocationId() {
        return locationId;
    }
    
    /**
     * Set Location ID Method
     * @param locationId -- sets this.locationid to id
     */
    public void setLocationId(int locationId) {
        this.locationId = locationId;
    }
    
    /**
     * Get Group ID Method
     * @return returns the group id of the event
     */
    public int getGroupId() {
        return groupId;
    }
    
    /**
     * Set Group ID Method
     * @param groupId -- sets this.groupId to groupId
     */
    public void setGroupId(int groupId) {
        this.groupId = groupId;
    }
    
    /**
     * Get Creator ID Method
     * @return -- returns the id of the event creator
     */
    public int getCreatorId() {
        return creatorId;
    }
    
    
    
    /**
     * Set Creator ID Method
     * @param creatorId --sets creatorId to creatorId
     */
    public void setCreatorId(int creatorId) {
        this.creatorId = creatorId;
    }
    
    /**
     * Get Warning Message Method
     * @return returns the warning message
     */
    public String getWarningMessage() {
        return warningMessage;
    }
    
    /**
     * Set Warning Mmessage Method
     * @param warningMessage --sets this.warningMessage to warningMessage
     */
    public void setWarningMessage(String warningMessage) {
        this.warningMessage = warningMessage;
    }
    
    /**
     * Set Event By ID Method
     * @param id -- id of event to be set in database
     * @throws java.sql.SQLException -- thrown if problem with database communication
     * @throws com.asaga.tools.WrongFormatException -- thrown if the date or time of event is of wrong format
     */
    public void setEventById(int id) throws SQLException, WrongFormatException{
        //eventSQL
        String eventSQL = "select eventID, title, startDate, endDate, startTime, endTime, description, " +
                "location_locationID, creator from event e, event_has_location el, person_has_event pe where e.eventID = " + id +
                " AND el.event_eventID = " + id +
                " AND pe.event_eventID = " + id;
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
            if(rs.next()){
                this.id = rs.getInt("eventID");
                title = rs.getString("title");
                startDate = revertDate(rs.getDate("startDate"));
                endDate = revertDate(rs.getDate("endDate"));
                startTime = revertTime(rs.getTime("startTime"));
                endTime = revertTime(rs.getTime("endTime"));
                description = rs.getString("description");
                locationId = rs.getInt("location_locationID");
                creatorId = rs.getInt("creator");
                getLocation().setLocationById(locationId);
            }
            else{
                warningMessage = "No Event with given id";
            }
        }
        catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage();
        }
        catch(WrongFormatException wfe){
            warningMessage = wfe.getMessage();
        }
        connectionPool.freeConnection(connection);
    }
    
    /**
     * Get Event By Id Method
     * @param id -- the id of the event
     * @return  -- returns a result set with the id
     */
    public ResultSet getEventById(int id){
        String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and" +
                " e.eventID = " + id;
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
        }
        catch(SQLException se){
                //stub.  will write to log file
        }
        return rs;
    }
    
    /**
     * Get All Events Method
     * @return -- returns Result Set of All Events
     */
    
    public ResultSet getAllEvents(){
         String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and e.startDate > (select current_date()) ORDER BY state";
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
        }
        catch(SQLException se){
            warningMessage="SQL Error: ";
        }
        return rs;
    }
    
    /**
     * Get Past Events Method
     * method gets events that have passed.
     * @return -- returns result set of past events
     */
    public ResultSet getPastEvents(){
         String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el" +
                " where e.eventID = el.event_eventID and" +
                " l.locationID = el.location_locationID and e.startDate < (select current_date()) ORDER BY state";
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
        }
        catch(SQLException se){
            warningMessage="SQL Error: ";
        }
        return rs;
    }        

    
    /**
     * Get Events By Attendant Method
     * @param id -- holds id of attendee
     * @return  -- returns set of events 
     */
    public ResultSet getEventsByAttendant(int id){
         String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el, person_has_event pe" +
                " where e.eventID = el.event_eventID and" +
                " e.eventID = pe.event_eventID AND " +
                " pe.person_personID = " + id + " AND" +
                " pe.creator != " + id + " AND" +
                " pe.willAttend = 1 AND" +
                " l.locationID = el.location_locationID AND" +
                " e.startDate > (select current_date()) ORDER BY startDate";
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
        }
        catch(SQLException se){
            warningMessage="SQL Error: ";
        }
        return rs;        
    }
    
    /**
     * Get Events By Person Method
     * @param id - the id of the creator
     * @return - returns a resultset of the events created by the person of specified id
     */
    public ResultSet getEventsByPersonId(int id){
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "select eventID, title, startDate, endDate, startTime, endTime, description" +
                " from event e, person_has_event pe where" +
                " e.eventID = pe.event_eventID AND" +
                " pe.person_personID = " + id + " AND" +
                " pe.creator = " + id + " AND" +
                " startDate > (select current_date()) order by startDate";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        return results;
    }
    
    /**
     * Get Events By State Method
     * @param theid --holds the id of the person to which the events will be filtered
     * @param state -- hold the state
     * @return -- returns a resultset of a query that returns all events by the given state
     */
    public ResultSet getEventsByState(String state, int theid) {
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "SELECT DISTINCT eventID, e.title AS event_title, startDate, endDate, description, l.city as location_city, l.state as location_state " +
                "FROM event e, person p, location l, event_has_location el, person_has_event pe WHERE " +
                "e.eventID = el.event_eventID AND " +
                "el.location_locationID = l.locationID AND " +
                "e.eventID = pe.event_eventID AND " + 
                "p.state = '" + state + "' AND " +
                "p.state = l.state AND " +
                "pe.creator != " + theid + " AND " +
                "pe.person_personID != " + theid + " AND " +
                "e.startDate >= (SELECT current_date()) order by startDate";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        
        return results;
    }

    /**
     * Get Location Method
     * @return returns the location object associated with the event
     */
    public Location_DBImpl getLocation() {
        return location;
    }

    /**
     * Set Location Method
     * @param location -- sets this.location to location
     */
    public void setLocation(Location_DBImpl location) {
        this.location = location;
    }

    ResultSet getEventByAttendant(int eventId, int id) {
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventSQL = "select * from person_has_event where" +
                " event_eventID = " + eventId + " AND" +
                " person_personID = " + id + " AND" +
                " willAttend = 1";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventSQL);
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        
        return results;
    }
    /**
     * Get Comments Method
     * method that gets the comments posted about an event
     * @return -- returns a result set of comments
     */
    public ResultSet getComments(){
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        String commentSQL = "select message, nickName, ec.date as thedate from event_has_comment ec, person p where" +
                " ec.event_eventID = " + id + " AND" +
                " ec.personID = p.personID";
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(commentSQL);
            connectionPool.freeConnection(connection);
        }
        catch(SQLException se){
            warningMessage = "SQL Error in getComments";
        }
        return rs;
    }

    void addComment(String comment, double rating, int userId) {
        
        //date check sql statement
        String dateSQL = "select endDate FROM event WHERE" +
                " eventID = " + id + " AND" + 
                " endDate < (select current_date())";
        
        //check attendance statment
        String attendanceSQL = "select * from person_has_event pe where" +
                " pe.person_personID = " + userId + " AND" +
                " pe.event_eventID = " + id + " AND" +
                " pe.willAttend = 1";
        
        //comment statement
        String commentSQL = "insert into event_has_comment (event_eventID, personID, message, date) values" +
                " (?,?,?, (select current_timestamp()))";
        
        //rating sql
        String rateSQL = "update person_has_event set rating = ? WHERE" +
                " person_personID = ? AND" +
                " event_eventID = ?";
        
        connection = connectionPool.getConnection();

        try{
            java.sql.PreparedStatement ps = connection.prepareStatement(rateSQL);
            java.sql.Statement stmt1 = connection.createStatement();
            java.sql.Statement stmt2 = connection.createStatement();
            ResultSet dateResults = stmt1.executeQuery(dateSQL);
            ResultSet attendanceResults = stmt2.executeQuery(attendanceSQL);           
            if(!dateResults.next()){
                warningMessage = "The event hasn't passed";
            }
            else if(!attendanceResults.next()){
                warningMessage = "You didn't attend this event";
            } 
            else{
                if(rating != 0){
                    ps.setDouble(1, rating);
                    ps.setInt(2, userId);
                    ps.setInt(3, id);
                    ps.executeUpdate();
                }
                
                ps = connection.prepareStatement(commentSQL);
                ps.setInt(1, id);
                ps.setInt(2, userId);
                ps.setString(3, SQLUtil.encode(comment));
                ps.executeUpdate();
            }
        }
        catch(SQLException se){
            warningMessage = "SQL Error:  " + se.getMessage();
        }
        
        connectionPool.freeConnection(connection);
    }//end addComment

    void updateEvent() {
        connection = connectionPool.getConnection();
        String updateEventSQL = "UPDATE event SET title = ?" +
                        ", startDate = ?, endDate = ?, startTime = ?, endTime = ?" +
                        ", description = ? where eventID = ?";
       /* String creatorSQL = "insert into person_has_event (person_personID, event_eventID, " +
                "dateCreated, creator, willAttend) values " +
                "(?,?, (SELECT CURRENT_TIMESTAMP()),?,?)";
        String locationSQL = "insert into event_has_location (event_eventID, location_locationID) values" +
                " (?,?)";
        String groupSQL = "insert into event_has_group (event_eventID, autismgroup_autismgroupID) values" +
                " (?,?)";*/
        try{
            //update event
            PreparedStatement ps = connection.prepareStatement(updateEventSQL);
            ps.setString(1, SQLUtil.encode(getTitle()));
            ps.setDate(2, (java.sql.Date)convertDate(getStartDate()));
            ps.setDate(3, (java.sql.Date)convertDate(getEndDate()));
            ps.setTime(4, convertTime(getStartTime()));
            ps.setTime(5, convertTime(getEndTime()));
            ps.setString(6, SQLUtil.encode(getDescription()));
            ps.setInt(7, id);
            ps.executeUpdate();
            warningMessage = "Event updated successfully.";
            
            //set new id
/*            setNewId();
            
            //insert person_has_event
            ps = connection.prepareStatement(creatorSQL);
            ps.setInt(1, creatorId);
            ps.setInt(2, id);
            ps.setInt(3, creatorId);
            ps.setInt(4, 1);
            ps.executeUpdate();
            
            //insert event_has_location
            ps = connection.prepareStatement(locationSQL);
            ps.setInt(1, id);
            ps.setInt(2, locationId);
            ps.executeUpdate();
            
            //insert event_has_group
            if(groupId != 0){
                ps = connection.prepareStatement(groupSQL);
                ps.setInt(1, id);
                ps.setInt(2, groupId);
                ps.executeUpdate();
            }

            warningMessage = "";*/
        } catch(WrongFormatException wfe){
            //wrong date format
            warningMessage = "wrong date format";
            //System.out.println(warningMessage + wfe.getMessage());
        }
        
        catch(SQLException se){
            //
            warningMessage = "wrong sql format";
            //System.out.println(warningMessage + se.getMessage());
        }
        
        connectionPool.freeConnection(connection);
    
    }
    
    /**
     * Get Rating Method
     * @return -- returns the result set of the average rating of a the event
     */
    public double getRating(){
        //int rating = 0;
        String ratingSQL = "select avg(rating) as average_rating from person_has_event where rating is not null";
        connection = connectionPool.getConnection();
        try{
            java.sql.Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(ratingSQL);
            if(rs.next()){
                rating = rs.getDouble("average_rating");
            }
            else{
                rating = 0.0;
            }
            
        }
        catch(SQLException se){
            warningMessage = "SQL Error in getRating";
        }
        return rating;
    }
    ResultSet getPastEventsByAttendant(int id) {
         String eventSQL = "select eventID, " +
                " e.title as event_title, l.title as location_title, startDate, endDate, startTime, endTime, city," +
                " state, description from event e, location l, event_has_location el, person_has_event pe" +
                " where e.eventID = el.event_eventID and" +
                " e.eventID = pe.event_eventID AND " +
                " pe.person_personID = " + id + " AND" +
                " pe.creator != " + id + " AND" +
                " pe.willAttend = 1 AND" +
                " l.locationID = el.location_locationID AND" +
                " e.endDate < (select current_date()) ORDER BY endDate";
        connection = connectionPool.getConnection();
        ResultSet rs = null;
        try{
            java.sql.Statement stmt = connection.createStatement();
            rs = stmt.executeQuery(eventSQL);
        }
        catch(SQLException se){
            warningMessage="SQL Error: ";
        }
        return rs;        
    }
     

    ResultSet getUpcomingEventsByPersonId(int theid) {
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "select eventID, title, startDate, endDate, startTime, endTime, description" +
                " from event e, person_has_event pe where" +
                " e.eventID = pe.event_eventID AND" +
                " pe.person_personID = " + theid + " AND" +
                " pe.creator = " + theid + " AND" +
                " e.startDate > (select current_date()) ORDER BY e.startDate";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        return results;
    }
        ResultSet getPastEventsByPersonId(int theid) {
        connection = connectionPool.getConnection();
        ResultSet results = null;
        String eventsSQL = "select eventID, title, startDate, endDate, startTime, endTime, description" +
                " from event e, person_has_event pe where" +
                " e.eventID = pe.event_eventID AND" +
                " pe.person_personID = " + theid + " AND" +
                " pe.creator = " + theid + " AND" +
                " e.endDate < (select current_date()) ORDER BY e.endDate";
        try{
            java.sql.Statement stmt = connection.createStatement();
            results = stmt.executeQuery(eventsSQL);
            connectionPool.freeConnection(connection);
        } catch(SQLException se){
            warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
        }
        return results;
    }
}
